
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertAnimalProtocolStaffMaps]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InsertAnimalProtocolStaffMaps]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.InsertAnimalProtocolStaffMaps
AS
BEGIN

	SET NOCOUNT ON

		
	-- ProtocolStaff_Map
		INSERT	ProtocolStaff_Map
			(	ProtocolNumber,
				ProtocolStaffTypeId,
				AdmPersonId,
				ModifiedDt,
				DateInserted,
				RexProtocolId,
				RecomAbstractId,
				OrganizationId,
				ModuleType, 
				ProtocolID,
				IsCurrent	 )
	Select 	Staff.sFolderNumber,
			Staff.ProtocolStaffingTypeID,
			Staff.AdmPersonId,
			Staff.dtModified,
			GETDATE(),
			Staff.nAnimalProtId,
			Staff.nRecomAbstractId,
			Staff.StaffOrganizationId,
			'A',
			Staff.ProtocolId,
			1
	FROM (Select Stg.sFolderNumber, Stg.ProtocolStaffingTypeID, Stg.AdmPersonId, Stg.dtModified, 
		Stg.dtInserted, Stg.nAnimalProtId, Stg.nRecomAbstractId, Stg.StaffOrganizationId, Stg.ProtocolId  
	From Stg_AnimalStudyStaff Stg
	left outer join ProtocolStaff_Map M
		on M.AdmPersonId = Stg.AdmPersonId
		and M.ProtocolNumber = Stg.sFolderNumber
		and M.ProtocolStaffTypeId = Stg.ProtocolstaffingTypeId
		and M.ModuleType = 'A' 
	WHERE M.Id is null
		AND Stg.ToBeInserted = 1) Staff
	WHERE Staff.AdmPersonID Is not null
	 
	-- Now handle the contact records
	insert into FwkResource_AdmPerson_Contact_Map ( AdmPersonId, FwkResourceId )

	Select	distinct(Staff.AdmPersonId), Prot.FwkResourceId
	FROM Stg_AnimalStudyStaff Staff
	inner join Protocol Prot
		ON Staff.ProtocolID = Prot.Id
	Where Prot.ModuleType = 'A' AND Staff.AdmPersonId is not null			
		and (Staff.bGetsMail = 1 OR Staff.ProtocolStaffingTypeID = 12)
		and not exists (select map.* from FwkResource_AdmPerson_Contact_Map Map
			Where Map.AdmPersonId = Staff.AdmPersonId and Map.FwkResourceId = Prot.FwkResourceId )
	--	and Staff.AdmPersonId in (select  Id from AdmPerson )
		and Prot.IsCurrent = 1

	-- Handle the PI for any records that don't have a PI staffed directly on them.
	INSERT	ProtocolStaff_Map
			(	ProtocolNumber,
				ProtocolStaffTypeId,
				AdmPersonId,
				ModifiedDt,
				DateInserted,
				RexProtocolId,
				OrganizationId,
				ModuleType, 
				ProtocolID,
				IsCurrent	 )
	Select	P.ProtocolNumber,
			13,
			P.PIPersonId,
			GETDATE(),
			GETDATE(),
			P.RexProtocolId,
			P.PIFwkDomainOrganizationId,
			'A',
			P.Id as ProtocolId,
			1
		FROM	Protocol P
		WHERE P.IsCurrent = 1
			AND ModuleType = 'A'
			AND P.MarkForDeletion = 0
		AND ProtocolNumber not in (
		SELECT Distinct(ProtocolNumber) FROM ProtocolStaff_Map 
		WHERE ModuleType = 'A' AND IsCurrent = 1 AND ProtocolStaffTypeId = 13
		AND ProtocolNumber IS NOT NULL )
		AND PIPersonID is NOT NULL
	
			
	SET NOCOUNT OFF

END



GO
